package dao;

import entity.Cliente;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

public class ClienteDAO {
    
    private Connection connection;
    
    public ClienteDAO () {
        this.connection = new ConnectionFactory().getConnection();
    }
    
    public void insere (Cliente cliente) {
        String sql = "INSERT INTO cliente (nome, sobrenome, rg, cpf, endereco, salario) VALUES (?, ?, ?, ?, ?, ?)";
        try {
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setString(1, cliente.getNome());
            stmt.setString(2, cliente.getSobrenome());
            stmt.setString(3, cliente.getRg());
            stmt.setString(4, cliente.getCpf());
            stmt.setString(5, cliente.getEndereco());            
            stmt.setDouble(6, cliente.getSalario());
            stmt.executeUpdate();
            stmt.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    
    public void altera (Cliente cliente) {       
        String sql = "UPDATE cliente SET nome=?, sobrenome=?, rg=?, cpf=?, endereco=?, salario=? WHERE codCliente = ?";
        try {
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setString(1, cliente.getNome());
            stmt.setString(2, cliente.getSobrenome());
            stmt.setString(3, cliente.getRg());
            stmt.setString(4, cliente.getCpf());
            stmt.setString(5, cliente.getEndereco());           
            stmt.setDouble(6, cliente.getSalario());
            stmt.setInt(7, cliente.getIdCliente());
            stmt.executeUpdate();
            stmt.close();
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
    }
    
    public void excluir_cliente (Cliente cliente) {
        String sql = "DELETE FROM cliente WHERE codCliente = ?";
        try {
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setInt(1, cliente.getIdCliente());
            stmt.executeUpdate();
            stmt.close();
        }catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
    }
    
    public List<Cliente> consultarCliente(Cliente client) {
        String sql = "SELECT * FROM cliente WHERE nome LIKE ? AND sobrenome LIKE ?"
                + " AND rg LIKE ? AND cpf LIKE ?";
        try {
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setString(1,"%" + client.getNome() + "%");
            stmt.setString(2,"%" + client.getSobrenome() + "%");
            stmt.setString(3,"%" + client.getRg() + "%");
            stmt.setString(4,"%" + client.getCpf() + "%");
            ResultSet rs = stmt.executeQuery();
            List<Cliente> clientes = new ArrayList();
            while(rs.next()){
                Cliente cliente = new Cliente();
                cliente.setNome(rs.getString("nome"));
                cliente.setSobrenome(rs.getString("sobrenome"));
                cliente.setCpf(rs.getString("cpf"));
                cliente.setRg(rs.getString("rg"));
                cliente.setEndereco(rs.getString("endereco"));   
                cliente.setSalario(rs.getDouble("salario"));        
                cliente.setIdCliente(rs.getInt("codCliente"));
                clientes.add(cliente);
            }
            return clientes;
            
        } catch (SQLException ex) {
            Logger.getLogger(ClienteDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

     public List<Cliente> buscarCliente(Cliente client) {
        String sql = "SELECT * FROM cliente WHERE cpf LIKE ? ";
        try {
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setString(1,"%" + client.getCpf() + "%");
            ResultSet rs = stmt.executeQuery();
            List<Cliente> clientes = new ArrayList();
            while(rs.next()){
                Cliente cliente = new Cliente();
                cliente.setNome(rs.getString("nome"));
                cliente.setSobrenome(rs.getString("sobrenome"));
                cliente.setCpf(rs.getString("cpf"));
                cliente.setRg(rs.getString("rg"));
                cliente.setEndereco(rs.getString("endereco"));   
                cliente.setSalario(rs.getDouble("salario"));        
                cliente.setIdCliente(rs.getInt("codCliente"));
                clientes.add(cliente);
            }
            return clientes;
            
        } catch (SQLException ex) {
            Logger.getLogger(ClienteDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
        
        
    public List<Cliente> listar() throws SQLException{
            String sql = "SELECT * FROM cliente";
            List<Cliente> clientes = new ArrayList();       
            PreparedStatement stmt = connection.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery();            
            while(rs.next()){
                Cliente cliente = new Cliente();
                cliente.setNome(rs.getString("nome"));
                cliente.setSobrenome(rs.getString("sobrenome"));
                cliente.setCpf(rs.getString("cpf"));
                cliente.setRg(rs.getString("rg"));
                cliente.setEndereco(rs.getString("endereco"));   
                cliente.setSalario(rs.getDouble("salario"));        
                cliente.setIdCliente(rs.getInt("codCliente"));
                clientes.add(cliente);
            }
            return clientes;
        
    }
    
//    As funçoes abaixo verificam que tipo de conta pertence o cliente, 0 para nenhum,
//    1 para invesitmento e dois para corrente
    
    public int verificainvestimento(int idcliente) throws SQLException{
       String sql = "SELECT cliente.codCliente FROM cliente, conta, containvestimento WHERE \n" +
              "cliente.codCliente = ? AND cliente.codCliente = conta.idCliente\n" +
              "AND conta.numConta = containvestimento.fkconta";
        PreparedStatement stmt = connection.prepareStatement(sql);
        stmt.setInt(1, idcliente);
        int retorno = 0;
        ResultSet rs = stmt.executeQuery(); 
        if(rs.first() == true)
            return 1;
        retorno = verificacorrente(idcliente);
        if(retorno == 2)
            return 2;
        else
            return 0;
     }
    
    public int verificacorrente(int idcliente) throws SQLException{
        String sql = "SELECT cliente.codCliente FROM cliente, conta, contacorrente WHERE \n" +
              "cliente.codCliente = ? AND cliente.codCliente = conta.idCliente\n" +
              "AND conta.numConta = contacorrente.fkconta";
        PreparedStatement stmt = connection.prepareStatement(sql);  
        stmt.setInt(1, idcliente);
        ResultSet rs = stmt.executeQuery(); 
        if(rs.first() == true)
            return 2;
        else
            return 0;
    }
}
